Mega Code Archive
SQL Plus
1) @
2) A dropped table is preserved in the recycle bin until purged
3) ACCEPT command creates a variable of datatype CHAR by default
4) Accept input and pass into a query
5) ACCEPT variable value from user input
6) Add title to report
7) Adding a group separator
8) Adjust your session time zone to -08
9) Aligning decimals
10) ALTER SESSION SET optimizer_dynamic_sampling
11) ALTER SESSION SET QUERY_REWRITE_ENABLED
12) ALTER SESSION SET SQL_TRACE = TRUE
13) Alter session set current_schema=Smart
14) Alter session set cursor_sharing = force
15) Alter session set db_file_multiblock_read_count
16) Alter session set hash_area_size
17) Alter session set nls_date_format = DD-MON-YYYY HH24
18) Alter session set NLS_DATE_FORMAT=DD-MM-YYYY
19) Alter session set nls_date_format=dd-mm-yyyy, nls_language=Dutch, nls_currency=Eur
20) Alter session set OPTIMIZER_MODE = RULE
21) Alter session set optimizer_index_cost_adj, alter session set optimizer_index_caching
22) Alter session set optimizer_max_permutations=80000
23) Alter session set optimizer_mode=all_rows
24) Alter session set optimizer_mode=first_rows
25) Alter session set query_rewrite_integrity=enforced
26) Alter session set sort_area_size = 102400000
27) Alter session set use_stored_outlines
28) Alter session set workarea_size_policy=manual
29) Alter system flush shared_pool
30) Alter system quiesce restricted
31) Alter system resume
32) Alter system suspend
33) Alter the session with the ALTER SESSION statement and set the sessions time zone forward
34) Alter time_zone
35) Analyze a table withwithout index
36) Analyze and autotrace full outer join and union
37) Analyze and autotrace single column key and multi-column key
38) Analyze and autotrace table with primary key
39) Analyze index
40) Analyze index on varchar2 value and date type value
41) Analyze index t_idx validate structure
42) Analyze index validate structure
43) Analyze materialized view
44) Analyze table after creating index
45) Analyze table compute statistics
46) Analyze table compute statistics for a table with two indexes
47) Analyze table compute statistics for all indexes
48) ANALYZE TABLE employee COMPUTE STATISTICS
49) Analyze table estimate statistics
50) Analyze table students compute statistics
51) Analyze table t compute statistics for table for columns id
52) Analyze table t compute statistics for table, for all indexes, for all indexed columns
53) Analyze table TABLENAME compute statistics
54) Analyze table tableName compute statistics for table, for all indexes, for all indexed columns
55) Analyze table with primary key and foreign key
56) Anayze all objects in the APPOWNER schema
57) Autotrace a large table
58) Autotrace a nested query
59) Autotrace a query on a huge table
60) Autotrace a query with group clause
61) Autotrace ansi full outer join
62) Autotrace command
63) Autotrace count()
64) Autotrace ctxsys context index
65) AUTOTRACE exists (subquery)
66) Autotrace lower text function
67) Autotrace merge command
68) Autotrace on and off
69) Autotrace running total
70) AUTOTRACE table joining
71) AUTOTRACE table joining and aggregate function
72) Autotrace table withwithout an index
73) Bind variables can be declared in your SQLPlus script,Preface a bind variable with a colon to reference it in a PLSQL block
74) Bind variables can even be referenced by SQL queries
75) Bottom title
76) Break on department skip 1 on job
77) Break on different columns
78) Check pre-defined variables
79) Check the new policy
80) Column data is aligned by type
81) COLUMN fname heading empName format a10
82) Column format
83) Column format $9,999 99
84) Column heading format a13
85) COLUMN id heading empNumber format 9999
86) Column localtimestamp format a28
87) Column number format
88) COLUMN Salary heading CurrentSalary format $9999 99
89) Common SQLPlus Environment Variables
90) Compute sum of cnt on report
91) Copy column format with col like
92) Create or replace outline and analyze table compute statistics
93) Define editor
94) Define editor Notepad or vi
95) Define variable and print its value out
96) Define variable and set value
97) Define variable and use it in sql statement
98) Demonstrate IN versus EXISTS performance
99) Demonstrates the use of DBMS_SQL to execute an ALTER SESSION statement
100) Disable the column formatting
101) Edit saved command
102) Event-based call and exception tracing
103) Executing a SQLPlus Command Script
104) Execution Plan
105) Execution plan for the specified statement_id
106) Exit or quit SQL PLUS
107) Explain plan for query
108) Explain plan for select statement
109) Explain sql statement
110) Extracts data from the emp table and writes it to a text file in a comma-delimited format
111) Flashback table e to before drop
112) GET
113) Get current program with sys_context(userenv,sessionid) and v$session
114) use the pause command to slow down the output displayed on your terminal
115) If your session time zone is not USCentral (-06
116) If your session time zone is not USCentral Standard Time (-06
117) Including a currency symbol
118) Interaction between ALTER SESSION and autonomous transactions
119) JUSTIFY CENTER
120) JUSTIFY RIGHT
121) Log in sqlplus as dba
122) Log in to sqlplus as dba
123) Loop timing
124) Make sure SQLPlus SERVEROUTPUT setting is on
125) Markup html entmap off
126) Output result to html table
127) Pause Press ENTER to begin
128) Performace difference between sql and plsql
129) PROMPT command outputs a line of data to the screen
130) Purge recyclebin
131) Query rewrite enabled
132) Read user input and use if to determine which table to print
133) Read value from prompt and output with plsql
134) Refcursor varible
135) Save command
136) Save returning value from a function to a variable
137) Saving SQL Commands to a File
138) Sessiontimezone, current_timestamp
139) Set autotrace on explain for every single statement
140) Set autotrace traceonly
141) Set autotrace traceonly explain for bitmap index
142) Set autotrace traceonly explain, and condition
143) Set autotrace traceonly statistics
144) Set autotrace traceonly statistics for select from tableName
145) Set column format before doing the query
146) Set column heading with column command
147) Set column separation with colsep
148) SET FEEDBACK OFFON
149) Set linesize 54
150) Set long
151) Set long 5000
152) Set markup html on
153) Set number column format
154) Set optimizer_features_enable = 8 1 5 scope = spfile
155) SET PAGESIZE 14, TTITLE OFF
156) Set pagesize 10
157) Set pagesize 100 linesize 120 newpage 0 feedback off
158) Set pagesize 30
159) Set pause message
160) Set pause off
161) Set pause text and show environment
162) Set report title
163) SET SERVEROUTPUT OFFON
164) SET SERVEROUTPUT ON SIZE 1000000
165) Set space 2
166) SET string to display when value is NULL
167) Set the following session parameters to enable query rewrite
168) Set the prompt
169) SHOW ALL command
170) Show define
171) Show feedback
172) Show filestat
173) Show markup
174) Show parameter optimizer_features
175) Show parameter timed_statistics
176) Show parameters block
177) SHOW USER
178) Show verify
179) Skip page
180) SPOOL query result to a file
181) Spool to a file with text string concatenation
182) Spool to report txt replace
183) Spooling files on and off
184) SQL trace
185) Sql_trace a stored procedure
186) START
187) Statistics query Timing on index
188) Sys_context(userenv,sessionid)
189) The SET SERVEROUTPUT Command
190) The SHOW SGA command shows the current allocations of the SGA memory
191) TIMING table copying
192) Time a query with where clause
193) Time and autotrace a big table
194) Time command
195) Time lower text function
196) Time query for a huge table
197) Timing passing table collection parameter to a procedure
198) Timing unconditional loop
199) TRUNCATED formatting option
200) Undefine variable
201) Use a13 to set the column length during displaying
202) Use accept to get user input value and use them in a select statement
203) Use format a30 heading to define column name
204) Use numformat to control the number format
205) Use prompt to display information
206) Use prompt to output message
207) Use SYS_CONTEXT to retrieve the database name
208) Use to run a command more than once
209) User defined-variable in update set statement
210) Userenv(sessionid)
211) USERENV(TERMINAL)
212) Uses dynamic SQL to issue an ALTER SESSION statement
213) Using the ACCEPT command
214) Using the Define Command
215) Word Wrapped column format
216) WORD_WRAPPED option for FORMAT wraps each line to the length specified by the value of the LINESIZE variable
217) Wrapping text